/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fjnu.bll;

import fjnu.dao.AbstractObject;
import fjnu.dao.Bulletinboard;
import fjnu.dao.GuidePost;
import fjnu.dao.Lamp;
import fjnu.dao.SportSequipment;
import fjnu.dao.Trash;
import fjnu.dao.factory.Factory;
import fjnu.dao.mysql.MysqlFactory;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 *
 * @author neco
 */
public class CreatExcel {

    private HashMap<String, TypeMapElement> findBorkenAssert() {
        ArrayList<Integer> IDList = new ArrayList<Integer>();
        ArrayList<String> IDStringList = new ArrayList<String>();
        HashMap<String, TypeMapElement> TypeMap = new HashMap<String, TypeMapElement>();

        Factory af = new MysqlFactory();

        IDList.addAll(af.getBulletinBoardMapper().getBrokenList());
        IDList.addAll(af.getGuidePostMapper().getBrokenList());
        IDList.addAll(af.getLampMapper().getBrokenList());
        IDList.addAll(af.getSportSequipmentMapper().getBrokenList());
        IDList.addAll(af.getTrashMapper().getBrokenList());
        IDStringList = changeInt(IDList);
        for (int id : IDList) {
            AbstractObject ao = Cache.getInstance().getObjectByID(id);
            if (ao != null) {

                String typeString = ao.getType();
                if (!TypeMap.containsKey(typeString)) {
                    TypeMap.put(typeString, new TypeMapElement(1, ao));
                }
                TypeMap.get(typeString).Plus();
                Integer temp = id;
                IDStringList.remove(temp.toString());
            }
        }
        for (String id : IDStringList) {

            int type = ExtractTypeFromID(id);
            LookUp lookUp = new LookUp();
            AbstractObject ao = lookUp.findObjectByIDNotCache(Integer.parseInt(id), type);
            String typeString = ao.getType();
            if (!TypeMap.containsKey(typeString)) {
                TypeMap.put(typeString, new TypeMapElement(1, ao));
            }
            TypeMap.get(typeString).Plus();
        }
        return TypeMap;
    }

    private int ExtractTypeFromID(int ID) {
        Integer id = ID;
        String Type = id.toString().substring(0, 1);
        int type = Integer.parseInt(Type);
        return type;
    }

    private int ExtractTypeFromID(String ID) {

        String Type = ID.substring(0, 1);
        int type = Integer.parseInt(Type);
        return type;
    }

    private RowElement changeToCell(TypeMapElement mTypeMapElement) {
        RowElement result = new RowElement();
        int id = mTypeMapElement.getAo().getID();
        int type = ExtractTypeFromID(id);
        AbstractObject ao = mTypeMapElement.getAo();
        switch (type) {
            case 1: {
                Lamp lamp = (Lamp) ao;
                result.setCategory(lamp.getLampType() + "型路灯");
                result.setType("品牌:" + lamp.getBrand() + "\n厂家型号：" + lamp.getModel() );
                result.setOther("功率：" + lamp.getPower() + "\n样式:" + lamp.getStyle() );
                result.setNote("颜色:" + lamp.getColor()+"\n灯泡数量：" + lamp.getLampCount());
            }
            ;
            break;
            case 2: {
                Trash trash = (Trash) ao;
                result.setCategory(trash.getType() + "型垃圾桶");
                result.setType("品牌:" + trash.getFactory() + "\n容量：" + trash.getCapacity() );
                result.setOther("使用年限：" + trash.getSerciceTime());
                result.setNote("材质:" + trash.getMaterial() + "\n规格:" + trash.getSpecifications());
            }
            ;
            break;
            case 3: {
                GuidePost guidepost = (GuidePost) ao;
                result.setCategory(guidepost.getContent() + "路牌");
                result.setType(guidepost.getModel());
                result.setOther("材质：" + guidepost.getMaterial());
            }
            ;
            break;
            case 4: {
                Bulletinboard bb = (Bulletinboard) ao;
                result.setCategory("公告牌：" + bb.getName());
                result.setOther("材质：" + bb.getMaterial());
            }
            ;
            break;
            case 5: {
                SportSequipment ss = (SportSequipment) ao;
                result.setCategory("运动设施:" + ss.getName());
                result.setOther("材质：" + ss.getMaterial() + "\n规格：" + ss.getSpecifications());
            }
            ;
            break;
            default:
        }
        result.setNum(mTypeMapElement.getCount());
        result.setPrice(mTypeMapElement.getAo().getPrice());
        result.setSupplier(mTypeMapElement.getAo().getSupplier());
        return result;
    }

    public InputStream getExcel() {
        HashMap<String, TypeMapElement> TypeMap = findBorkenAssert();
        ArrayList<RowElement> rows = new ArrayList<RowElement>();
        Iterator iterator = TypeMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, TypeMapElement> entry = (Map.Entry) iterator.next();
            rows.add(changeToCell(entry.getValue()));
        }
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("采购报表");
        sheet.setDefaultColumnWidth((short)14);

        

        /**
         * 样式部分  标题
         */
        Font titlefont = wb.createFont();
        titlefont.setFontHeightInPoints((short) 14);
        titlefont.setFontName("simhei");
        // titlefont.setItalic(false);
        //  titlefont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle titlestyle = wb.createCellStyle();
        titlestyle.setFont(titlefont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
     //  titlestyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        /**
         * 样式部分副标题
         */
        Font titlefont2 = wb.createFont();
        titlefont2.setFontHeightInPoints((short) 12);
        titlefont2.setFontName("simhei");
        //   titlefont2.setItalic(false);
        // titlefont2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle titlestyle2 = wb.createCellStyle();
        titlestyle2.setFont(titlefont2);


        /**
         * 样式部分  内容
         */
        Font contentfont2 = wb.createFont();
        contentfont2.setFontHeightInPoints((short) 10);
      //  contentfont2.setFontName("simhei");
        //   titlefont2.setItalic(false);
        //contentfont2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle contentStyle = wb.createCellStyle();
        contentStyle.setWrapText(true);
        contentStyle.setFont(contentfont2);
        contentStyle.setAlignment(CellStyle.ALIGN_LEFT);
        contentStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);



        /*
         * 开始写入内容
         */
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0, 0);
        cell.setCellValue("采购报表");
        cell.setCellStyle(titlestyle);
        row.setHeightInPoints((short)18);

        sheet.addMergedRegion(new CellRangeAddress(
                0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                7 //last column  (0-based)
                ));


        /**
         *  副标题
         */
        Row Title = sheet.createRow(1);

        Cell temp = Title.createCell(0);
        temp.setCellValue("类别");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(1);
        temp.setCellValue("型号");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(2);
        temp.setCellValue("供应商");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(3);
        temp.setCellValue("数量");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(4);
        temp.setCellValue("单价");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(5);
        temp.setCellValue("总价");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(6);
        temp.setCellValue("其他");
        temp.setCellStyle(titlestyle2);
        temp = Title.createCell(7);
        temp.setCellValue("备注");
        temp.setCellStyle(titlestyle2);


        /**
         * 内容部分
         *
         */
        int i = 2;
        float totalP=0.0f;
        for (RowElement re : rows) {
            Row content = sheet.createRow(i);

            if(i%2==0)
                contentStyle.setFillPattern(IndexedColors.GREY_25_PERCENT.getIndex());
            content.setHeightInPoints(55.0f);

            Cell contentCell = content.createCell(0);
            contentCell.setCellValue(re.Category);
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(1);
            contentCell.setCellValue(re.Type);
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(2);
            contentCell.setCellValue(re.Supplier);
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(3);
            contentCell.setCellValue(re.num);
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(4);
            contentCell.setCellValue(re.price);
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(5);
            contentCell.setCellValue(re.getTotalPrice());
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(6);
            contentCell.setCellValue(re.other);
            contentCell.setCellStyle(contentStyle);

            contentCell = content.createCell(7);
            contentCell.setCellValue(re.note);
            contentCell.setCellStyle(contentStyle);
            totalP=totalP+re.getTotalPrice();
            i++;
        }

       Row total = sheet.createRow(i);
       Cell cellTotal=total.createCell(5);
       cellTotal.setCellValue("总价");

       Cell cellTotal2=total.createCell(6);
       cellTotal2.setCellValue(totalP+"元");


        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        return is;
    }

    private ArrayList<String> changeInt(ArrayList<Integer> List) {
        ArrayList<String> result = new ArrayList<String>();
        for (int i : List) {
            Integer temp = i;
            result.add(temp.toString());
        }
        return result;
    }

    public static void main(String[] Args) throws Exception {
        CreatExcel ce = new CreatExcel();
        ce.getExcel();

    }

    class RowElement {

        protected String Category;
        protected String Type;
        protected String Supplier;
        protected float price;
        protected int num;
        protected String other;
        protected String note;

        /**
         * Get the value of note
         *
         * @return the value of note
         */
        public String getNote() {
            return note;
        }

        /**
         * Set the value of note
         *
         * @param note new value of note
         */
        public void setNote(String note) {
            this.note = note;
        }

        /**
         * Get the value of other
         *
         * @return the value of other
         */
        public String getOther() {
            return other;
        }

        /**
         * Set the value of other
         *
         * @param other new value of other
         */
        public void setOther(String other) {
            this.other = other;
        }

        /**
         * Get the value of totalPrice
         *
         * @return the value of totalPrice
         */
        public float getTotalPrice() {
            return price * num;
        }

        /**
         * Get the value of num
         *
         * @return the value of num
         */
        public int getNum() {
            return num;
        }

        /**
         * Set the value of num
         *
         * @param num new value of num
         */
        public void setNum(int num) {
            this.num = num;
        }

        /**
         * Get the value of price
         *
         * @return the value of price
         */
        public float getPrice() {
            return price;
        }

        /**
         * Set the value of price
         *
         * @param price new value of price
         */
        public void setPrice(float price) {
            this.price = price;
        }

        /**
         * Get the value of Supplier
         *
         * @return the value of Supplier
         */
        public String getSupplier() {
            return Supplier;
        }

        /**
         * Set the value of Supplier
         *
         * @param Supplier new value of Supplier
         */
        public void setSupplier(String Supplier) {
            this.Supplier = Supplier;
        }

        /**
         * Get the value of Type
         *
         * @return the value of Type
         */
        public String getType() {
            return Type;
        }

        /**
         * Set the value of Type
         *
         * @param Type new value of Type
         */
        public void setType(String Type) {
            this.Type = Type;
        }

        /**
         * Get the value of Category
         *
         * @return the value of Category
         */
        public String getCategory() {
            return Category;
        }

        /**
         * Set the value of Category
         *
         * @param Category new value of Category
         */
        public void setCategory(String Category) {
            this.Category = Category;
        }
    }

    class TypeMapElement {

        private int Count;
        private AbstractObject ao;

        public TypeMapElement() {
        }

        public TypeMapElement(int Count, AbstractObject ao) {
            this.Count = Count;
            this.ao = ao;
        }

        public void Plus() {
            this.Count++;
        }

        /**
         * Get the value of ao
         *
         * @return the value of ao
         */
        public AbstractObject getAo() {
            return ao;
        }

        /**
         * Set the value of ao
         *
         * @param ao new value of ao
         */
        public void setAo(AbstractObject ao) {
            this.ao = ao;
        }

        /**
         * Get the value of Count
         *
         * @return the value of Count
         */
        public int getCount() {
            return Count;
        }

        /**
         * Set the value of Count
         *
         * @param Count new value of Count
         */
        public void setCount(int Count) {
            this.Count = Count;
        }
    }
}
